﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using Sinacor.Infra.Service.Data;
using Sinacor.Service.Infra.AuthorizationService.BusinessEntity;

namespace Sinacor.Service.Infra.AuthorizationService.ResourceAccess
{
    public class Menu
    {
        public static List<Company> RetrieveCompanies(string loginName, Int32 userId)
        {
            StringBuilder SQL = new StringBuilder();
            SQL.Append(@"SELECT DISTINCT 
						    	EMP.CD_EMPRESA, 
								EMP.NM_EMPRESA, 
								NVL((SELECT CD_CGC FROM TGELOCAL WHERE IN_PRAC_PRINC = 'S' AND CD_EMPRESA = EMP.CD_EMPRESA), '0') as CD_CGC, 
                                EMP.CD_USUA_ORAC
							FROM 
								TGEEMPR EMP 
							INNER JOIN 
								TGEUSUEMP USUEMP 
							ON 
								EMP.CD_EMPRESA = USUEMP.CD_EMPRESA
							INNER JOIN 
								TGEUSUARIO USU 
							ON 
								USU.CD_USUARIO = USUEMP.CD_USUARIO");

            if (!string.IsNullOrEmpty(loginName))
                SQL.Append(" WHERE USU.CD_LOGIN = :loginName");
            else
                SQL.Append(" WHERE USU.CD_USUARIO = :userId");

            SQL.Append(" ORDER BY EMP.NM_EMPRESA");

            List<Company> companies = new List<Company>();

            OracleDataBase dbCompany = new OracleDataBase();

            using (DbCommand cmd = dbCompany.GetSqlStringCommand(SQL.ToString()))
            {
                if (!string.IsNullOrEmpty(loginName))
                    dbCompany.AddInParameter(cmd, "loginName", DbType.String, loginName);
                else
                    dbCompany.AddInParameter(cmd, "userId", DbType.Int32, userId);

                using (IDataReader dr = dbCompany.ExecuteReader(cmd))
                {
                    if (dr.Read())
                    {
                        do
                        {
                            Company company = new Company();
                            company.Id = (int)dr.GetDecimal(0);
                            company.Name = dr.GetString(1);
                            company.CGC = dr.GetString(2);
                            company.DbInstance = Sinacor.Infra.Service.Data.Connections.GetDefaultInstance();
                            company.DbUser = dr.GetString(3);
                            companies.Add(company);
                        }
                        while (dr.Read());
                    }
                }
            }

            return companies;
        }

        public static List<Module> RetrieveModules(int userId, int companyId)
        {
            StringBuilder SQL = new StringBuilder();
            SQL.Append(@"
						SELECT DISTINCT 
							SIS.CD_SISTEMA
							,SIS.DS_SISTEMA 
						FROM 
							TGESIST SIS 
						INNER JOIN 
							TGESINST INST 
						ON 
							INST.CD_SISTEMA = SIS.CD_SISTEMA 
						INNER JOIN 
							TCASISUSU SISUSU 
						ON 
							SISUSU.CD_SISTEMA = SIS.CD_SISTEMA 
						INNER JOIN 
							TFWMODULOSINST MDINST 
						ON 
							MDINST.CD_SISTEMA = SIS.CD_SISTEMA 
						WHERE 
							SISUSU.IN_BLOQUEIO = 'N' 
							AND SIS.IN_BLOQUEIO='N' 
							AND SISUSU.CD_USUARIO = :CD_USUARIO

						UNION SELECT DISTINCT 
							SIS.CD_SISTEMA
							,SIS.DS_SISTEMA 
						FROM 
							TGESIST SIS 
						INNER JOIN 
							TGESINST INST 
						ON 
							INST.CD_SISTEMA = SIS.CD_SISTEMA 
						INNER JOIN 
							TCASISGRP SISGRP 
						ON 
							SISGRP.CD_SISTEMA = SIS.CD_SISTEMA 
						INNER JOIN 
							TCAUSUGRP USUGRP 
						ON 
							USUGRP.CD_GRUPO = SISGRP.CD_GRUPO 
						WHERE 
							SISGRP.IN_BLOQUEIO = 'N' 
							AND SIS.IN_BLOQUEIO = 'N' 
							AND USUGRP.CD_USUARIO = :CD_USUARIO
			");

            List<Module> modules = new List<Module>();

            OracleDataBase dbModule = new OracleDataBase(companyId);

            using (DbCommand cmd = dbModule.GetSqlStringCommand(SQL.ToString()))
            {
                dbModule.AddInParameter(cmd, "CD_USUARIO", DbType.Int32, userId);

                using (IDataReader dr = dbModule.ExecuteReader(cmd))
                {
                    if (dr.Read())
                    {
                        do
                        {
                            Module module = new Module();
                            module.Id = dr.GetString(0);
                            module.Name = dr.GetString(1);
                            modules.Add(module);
                        }
                        while (dr.Read());
                    }
                }
            }

            return modules;
        }

        public static List<Role> RetrieveRoles(string systemId, int userId, int companyId)
        {
            List<Role> roles = new List<Role>();

            OracleDataBase db = new OracleDataBase(companyId);

            using (DbCommand cm = db.GetStoredProcCommand("proc_FW.spfw_retornaroles"))
            {
                db.AddInParameter(cm, "p_cod_sistema", DbType.AnsiString, systemId);
                db.AddInParameter(cm, "p_cod_usuario", DbType.Int32, userId);

                using (IDataReader dr = db.ExecuteReader(cm, "p_cursor"))
                {
                    while (dr.Read())
                    {
                        roles.Add(new Role() { Name = dr.GetString(0) });
                    }
                }
            }

            return roles;
        }

        public static List<MenuItem> RetrieveMenu(string systemId, int userId, int companyId)
        {
            List<MenuItem> menus = new List<MenuItem>();

            OracleDataBase db = new OracleDataBase(companyId);

            using (DbCommand cm = db.GetStoredProcCommand("proc_FW.spfw_retornaMenu"))
            {
                db.AddInParameter(cm, "p_cod_sistema", DbType.AnsiString, systemId);
                db.AddInParameter(cm, "p_cod_usuario", DbType.Int32, userId);

                using (IDataReader dr = db.ExecuteReader(cm, "p_cursor"))
                {

                    DataTable dt = new DataTable();

                    //Carrega o reader para memória
                    dt.Load(dr);

                    foreach (DataRow row in dt.Select("CD_MENUSUP IS NULL"))
                    {
                        MenuItem mi = CreateMenuItem(row);
                        mi.Children = GetChilds(mi, dt);
                        menus.Add(mi);
                    }
                }
            }

            return menus;
        }

        private static MenuItem CreateMenuItem(DataRow dr)
        {
            MenuItem menu = new MenuItem();
            menu.Id = Int32.Parse(dr[0].ToString());
            menu.Name = dr[2].ToString();
            menu.Assembly = dr[3].ToString();
            menu.Classe = dr[4].ToString();
            menu.Role = dr[6].ToString();
            return menu;
        }

        private static List<MenuItem> GetChilds(MenuItem parent, DataTable tb)
        {
            List<MenuItem> childs = new List<MenuItem>();
            foreach (DataRow row in tb.Select("CD_MENUSUP = " + parent.Id.ToString()))
            {
                MenuItem mi = CreateMenuItem(row);
                mi.Children = GetChilds(mi, tb);
                childs.Add(mi);
            }
            return childs;
        }
    }
}